﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using ModelLayer.Reports;
using DataAccess;

namespace DataAccessLayer.Reports
{
    public class ReportDataAccess
    {

        public static void InsertNewDictation(Report r)
        {
            GestorBD.InsertNewDictation_SP(r);
        }

        public static Guid GetReportOIDOfProcedureOID(Guid ProcedureOID)
        {
            GestorBD.Query = "SELECT ReportOID_ FROM ProcedureReport_ WHERE ProcedureOID_='"+ProcedureOID+"' ";
            return Guid.Parse(GestorBD.EjecutarSentencia().Rows[0][0].ToString());
        }

        public static void UpdateStatusOfDictations(Guid reportPartOID, string status)
        {
            GestorBD.Query = "UPDATE ReportPart_ SET Status_ = '" + status + "' WHERE OID_ = '" + reportPartOID + "'";
            GestorBD.Actualizacion();
        }

        public static void DeleteDictation(Guid reportPartOID, string status, Guid reportOID_, int index)
        {
            GestorBD.Query = "UPDATE ReportPart_ SET Status_ = '" + status + "' WHERE OID_ = '" + reportPartOID + "'";
            GestorBD.Actualizacion();

            GestorBD.Query = "UPDATE ReportPart_ SET Version_ = 3, Index_=0  WHERE ReportOID_ = '" + reportOID_ + "' and Index_ = '" + index + "' and Version_ <> 1";
            GestorBD.Actualizacion();
        }

        public static void RejectInform(Guid ReportPartOID_, string status, string reason)
        {
            GestorBD.Query = "UPDATE ReportPart_ SET Status_ = '" + status + "', TranscriptionRejectReason_='" + reason + "' WHERE OID_ = '" + ReportPartOID_ + "'";
            GestorBD.Actualizacion();
        }

        public static string GetPathOfReportPartOid(Guid ReportPartOID)
        {
            GestorBD.Query = "SELECT Value_ FROM ReportPartExtendedProperty_ WHERE ReportPartOID_='" + ReportPartOID + "'";
            return GestorBD.EjecutarSentencia().Rows[0].ItemArray[0].ToString();
        }

        public static System.Data.DataTable GetRadiologists()
        {
            GestorBD.Query = "SELECT U.OID_,U.UserName_,S.OID_,S.GivenName_,S.FamilyName_ FROM User_ U, Staff_ S where U.UserName_= S.UserName_ and S.Type_='PRAD'";
            return GestorBD.EjecutarSentencia();


        }

        public static System.Data.DataTable GetModalities()
        {
            //string codeFacility = "CLO";
            //GestorBD.Query = "SELECT Description_ FROM Location_ WHERE Location_.Deactivated_='False' AND Location_.FacilityOID_ = (SELECT OID_ FROM Facility_ WHERE Code_ = '" + codeFacility + "') ORDER BY (Location_.Name_)";
            GestorBD.Query = "SELECT Name_ FROM ProcedureTypeGroup_ WHERE ProcedureTypeGroup_.Discriminator_='PERFORMING' ORDER BY ProcedureTypeGroup_.Name_ ";
            return GestorBD.EjecutarSentencia();
        }

        public static System.Data.DataTable GetPatientsTable(string lastName, string name, string pid, string assignedPractitioner, string modality)
        {
            if (assignedPractitioner != "" && modality == "")
                GestorBD.Query = "SELECT DISTINCT pp.OID_, pp.PatientOID_, pp.FamilyName_, pp.GivenName_, pp.MiddleName_, pp.DateOfBirth_, pp.Sex_, pp.MrnId_ FROM PatientProfile_ pp, Order_ o, Visit_ v WHERE o.PatientOID_ = pp.PatientOID_ AND pp.PatientOID_ = v.PatientOID_ AND o.AssignedPractitioner_ = '" + assignedPractitioner + "'  AND pp.FamilyName_ LIKE '" + lastName + "%' AND pp.GivenName_ LIKE '" + name + "%' AND pp.MrnId_ LIKE '" + pid + "%'";
            else if (assignedPractitioner == "" && modality != "")
                GestorBD.Query = "SELECT DISTINCT pp.OID_, pp.PatientOID_, pp.FamilyName_, pp.GivenName_, pp.MiddleName_, pp.DateOfBirth_, pp.Sex_, pp.MrnId_ FROM PatientProfile_ pp, Order_ o, Visit_ v WHERE o.PatientOID_ = pp.PatientOID_ AND pp.PatientOID_ = v.PatientOID_ AND o.DiagnosticServiceOID_ = (SELECT ds.OID_ FROM DiagnosticService_ ds WHERE ds.Name_ = '" + modality + "' )AND pp.FamilyName_ LIKE '" + lastName + "%' AND pp.GivenName_ LIKE '" + name + "%' AND pp.MrnId_ LIKE '" + pid + "%'";
            else if (assignedPractitioner != "" && modality != "")
                GestorBD.Query = "SELECT DISTINCT pp.OID_, pp.PatientOID_, pp.FamilyName_, pp.GivenName_, pp.MiddleName_, pp.DateOfBirth_, pp.Sex_, pp.MrnId_ FROM PatientProfile_ pp, Order_ o, Visit_ v WHERE o.PatientOID_ = pp.PatientOID_ AND pp.PatientOID_ = v.PatientOID_ AND o.AssignedPractitioner_ = '" + assignedPractitioner + "' AND o.DiagnosticServiceOID_ = (SELECT ds.OID_ FROM DiagnosticService_ ds WHERE ds.Name_ = '" + modality + "' )AND pp.FamilyName_ LIKE '" + lastName + "%' AND pp.GivenName_ LIKE '" + name + "%' AND pp.MrnId_ LIKE '" + pid + "%'";
            else if (assignedPractitioner == "" && modality == "")
                GestorBD.Query = "SELECT DISTINCT pp.OID_, pp.PatientOID_, pp.FamilyName_, pp.GivenName_, pp.MiddleName_, pp.DateOfBirth_, pp.Sex_, pp.MrnId_ FROM PatientProfile_ pp, Order_ o, Visit_ v WHERE pp.FamilyName_ LIKE '" + lastName + "%' AND pp.GivenName_ LIKE '" + name + "%' AND pp.MrnId_ LIKE '" + pid + "%'";
            return GestorBD.EjecutarSentencia();
        }

        public static System.Data.DataTable GetPatientsTableWithDate(string lastName, string name, string pid, string assignedPractitioner, string modality, DateTime date)
        {
            
            if (assignedPractitioner != "" && modality == "")
                GestorBD.Query = "SELECT DISTINCT pp.OID_, pp.PatientOID_, pp.FamilyName_, pp.GivenName_, pp.MiddleName_, pp.DateOfBirth_, pp.Sex_, pp.MrnId_ FROM PatientProfile_ pp, Order_ o, Visit_ v WHERE o.PatientOID_ = pp.PatientOID_ AND pp.PatientOID_ = v.PatientOID_ AND o.AssignedPractitioner_ = '" + assignedPractitioner + "'  AND pp.FamilyName_ LIKE '" + lastName + "%' AND pp.GivenName_ LIKE '" + name + "%' AND pp.MrnId_ LIKE '" + pid + "%' AND CONVERT(varchar(20), o.EnteredTime_, 103) = '" + String.Format("{0:dd/MM/yyyy}", date) + "'";
            else if (assignedPractitioner == "" && modality != "")
                GestorBD.Query = "SELECT DISTINCT pp.OID_, pp.PatientOID_, pp.FamilyName_, pp.GivenName_, pp.MiddleName_, pp.DateOfBirth_, pp.Sex_, pp.MrnId_ FROM PatientProfile_ pp, Order_ o, Visit_ v WHERE o.PatientOID_ = pp.PatientOID_ AND pp.PatientOID_ = v.PatientOID_ AND o.DiagnosticServiceOID_ = (SELECT ds.OID_ FROM DiagnosticService_ ds WHERE ds.Name_ = '" + modality + "' )AND pp.FamilyName_ LIKE '" + lastName + "%' AND pp.GivenName_ LIKE '" + name + "%' AND pp.MrnId_ LIKE '" + pid + "%' AND CONVERT(varchar(20), o.EnteredTime_, 103) = '" + String.Format("{0:dd/MM/yyyy}", date) + "'";
            else if (assignedPractitioner != "" && modality != "")
                GestorBD.Query = "SELECT DISTINCT pp.OID_, pp.PatientOID_, pp.FamilyName_, pp.GivenName_, pp.MiddleName_, pp.DateOfBirth_, pp.Sex_, pp.MrnId_ FROM PatientProfile_ pp, Order_ o, Visit_ v WHERE o.PatientOID_ = pp.PatientOID_ AND pp.PatientOID_ = v.PatientOID_ AND o.AssignedPractitioner_ = '" + assignedPractitioner + "' AND o.DiagnosticServiceOID_ = (SELECT ds.OID_ FROM DiagnosticService_ ds WHERE ds.Name_ = '" + modality + "' )AND pp.FamilyName_ LIKE '" + lastName + "%' AND pp.GivenName_ LIKE '" + name + "%' AND pp.MrnId_ LIKE '" + pid + "%' AND CONVERT(varchar(20), o.EnteredTime_, 103) = '" + String.Format("{0:dd/MM/yyyy}", date) + "'";
            else if (assignedPractitioner == "" && modality == "")
                GestorBD.Query = "SELECT DISTINCT pp.OID_, pp.PatientOID_, pp.FamilyName_, pp.GivenName_, pp.MiddleName_, pp.DateOfBirth_, pp.Sex_, pp.MrnId_ FROM PatientProfile_ pp, Order_ o, Visit_ v WHERE o.PatientOID_ = pp.PatientOID_ AND pp.PatientOID_ = v.PatientOID_ AND pp.FamilyName_ LIKE '" + lastName + "%' AND pp.GivenName_ LIKE '" + name + "%' AND pp.MrnId_ LIKE '" + pid + "%' AND CONVERT(varchar(20), o.EnteredTime_, 103) = '" + String.Format("{0:dd/MM/yyyy}", date) +"'";
            
            return GestorBD.EjecutarSentencia();
        }
    }
}
